﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace QuantitativeInvestment.Tools
{
    class DataModel
    {
        public SqlConnection windConnection; // 数据库连接dd
        public SqlCommand windCommand; //数据库连接命令

        public DataModel()
        {
            this.windConnection = new SqlConnection("Server=localhost; User=sa; PWD=111111;database=DFZQ;Asynchronous Processing=true");
            this.windConnection.Open();
            this.windCommand = this.windConnection.CreateCommand();
        }
        public DataModel(string server, string user, string password, string database)
        {
            this.windConnection = new SqlConnection("Server=" + server + "; User=" + user + "; PWD=" + password + ";database=" + database + ";Asynchronous Processing=true");
            this.windConnection.Open();
            this.windCommand = this.windConnection.CreateCommand();
        }


        //得到交易日的列表
        public List<string> getTradingDateList(string startDate = "20000101", string endDate = "20111231")
        {
            windCommand.CommandText = "SELECT tradingDate FROM tradingDates where tradingDate>=" + startDate + " and tradingDate<=" + endDate + " order by tradingDate asc";

            SqlDataReader sdr = windCommand.ExecuteReader();
            List<string> tradingDateList = new List<string>();
            while (sdr.Read())
            {
                tradingDateList.Add(sdr["tradingDate"].ToString());
            }
            sdr.Close();
            return tradingDateList;
        }
        //得到板块列表
        public Dictionary<int,string> getSectorList(string sectorType, int  sectorLevel)
        {
            windCommand.CommandText = "SELECT [sectorCode] ,[sectorName]  FROM [DFZQ].[dbo].[sectorList] where sectorType='" + sectorType + "' and sectorLevel="+sectorLevel;
            SqlDataReader sdr = windCommand.ExecuteReader();
            Dictionary<int, string> sectorList = new Dictionary<int, string>();
            while (sdr.Read())
            {
                sectorList.Add(Int32.Parse(sdr["sectorCode"].ToString()), sdr["sectorName"].ToString());
            }
            sdr.Close();
            return sectorList;
        }
        //
        public double[] getStockSectorCodeList(string sectorType, int sectorLevel,string stockCode)
        {
            windCommand.CommandText = "SELECT sectorContent.sectorCode FROM [DFZQ].[dbo].[stockDailyDerivative],sectorContent,sectorList where code='" + stockCode + "' and  sectorList.sectorType='" + sectorType + "' and sectorLevel=" + sectorLevel + " and sectorContent.sectorCode=sectorList.sectorCode  and tradingDate>startDate and (endDate is null or tradingDate<endDate) and code=stockCode and turnOver>0 order by tradingDate asc ";
            SqlDataAdapter sqlAdapter = new SqlDataAdapter(this.windCommand.CommandText, this.windConnection);
            DataTable dt = new DataTable();
            sqlAdapter.Fill(dt);
            double[] codeList = new double[dt.Rows.Count];
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                codeList[i] = Double.Parse(dt.Rows[i]["sectorCode"].ToString());
            }
            return codeList;
        }
        //得到个股日收益率的序列
        public double[] getStockDailyIncreaseList(string startDate, string endDate, string code)
        {
            windCommand.CommandText = "SELECT [increase] FROM [DFZQ].[dbo].[stockDailyDerivative] where code='" + code + "' and tradingDate>=" + startDate + " and  tradingDate<=" + endDate + " order by tradingDate";
            SqlDataAdapter sqlAdapter = new SqlDataAdapter(this.windCommand.CommandText, this.windConnection);
            DataTable dt = new DataTable();
            sqlAdapter.Fill(dt);

            double[] increaseList = new double[dt.Rows.Count];
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                increaseList[i] = Double.Parse(dt.Rows[i]["increase"].ToString());
            }
            return increaseList;
        }

        //得到指数每日收益率序列
        public double[] getIndexDailyIncreaseList(string startDate, string endDate, string code)
        {
            windCommand.CommandText = "SELECT (closePrice-lastClosePrice)/lastClosePrice as increase  FROM [DFZQ].[dbo].[indexDaily] where indexCode='" + code + "' and tradingDate>=" + startDate + " and  tradingDate<=" + endDate + " order by tradingDate asc";
            SqlDataAdapter sqlAdapter = new SqlDataAdapter(this.windCommand.CommandText, this.windConnection);
            DataTable dt = new DataTable();
            sqlAdapter.Fill(dt);

            double[] increaseList = new double[dt.Rows.Count];
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                increaseList[i] = Double.Parse(dt.Rows[i]["increase"].ToString());
            }
            return increaseList;
        }

        //得到指数每日收益率序列
        public double getIndexIncreaseByName(string startDate, string endDate, string name)
        {
            windCommand.CommandText = "SELECT  [closePrice]   FROM [DFZQ].[dbo].[indexDaily],indexList where [indexDaily].[indexCode]=indexList.[indexCode] and indexName like '" + name + "' and tradingDate='" + startDate + "'";
            SqlDataReader sdr = windCommand.ExecuteReader();
            double startPrice = 0;
            while (sdr.Read())
            {
                startPrice = Double.Parse(sdr["closePrice"].ToString());
            }
            sdr.Close();

            windCommand.CommandText = "SELECT  [closePrice]   FROM [DFZQ].[dbo].[indexDaily],indexList where [indexDaily].[indexCode]=indexList.[indexCode] and indexName like '" + name + "' and tradingDate='" + endDate + "'";
            sdr = windCommand.ExecuteReader();
            double endPrice = 0;
            while (sdr.Read())
            {
                endPrice = Double.Parse(sdr["closePrice"].ToString());
            }
            sdr.Close();

            return (endPrice - startPrice) / startPrice;
        }

        //得到单个股票某日复权收盘价
        public double[] getAdjustedStockPriceList(string startDate, string endDate, string code, string priceType = "收盘价")
        {
            if(priceType=="收盘价")
                windCommand.CommandText = "SELECT  closePrice as price ";
            else if(priceType=="开盘价")
                windCommand.CommandText = "SELECT  openPrice as price";
            else if(priceType=="最高价")
                windCommand.CommandText = "SELECT  highPrice as price ";
            else if(priceType=="最低价")
                windCommand.CommandText = "SELECT  lowPrice as price ";
            else if(priceType=="均价")
                windCommand.CommandText = "SELECT  avgPrice  as price ";


            windCommand.CommandText = windCommand.CommandText + "  FROM [DFZQ].[dbo].[stockDailyDerivative] where  code='" + code + "' and volume>0 and  tradingDate>='" + startDate + "' and tradingDate<='" + endDate + "' order by tradingDate asc ";
            SqlDataAdapter sqlAdapter = new SqlDataAdapter(this.windCommand.CommandText, this.windConnection);
            DataTable dt = new DataTable();
            sqlAdapter.Fill(dt);

            double[] closePriceList = new double[dt.Rows.Count];
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                closePriceList[i] = Double.Parse(dt.Rows[i]["price"].ToString());
            }
            return closePriceList;
        }
       

        //得到流通市值序列
        public double[] getStockMarketValueInCurrencyList(string startDate, string endDate, string code)
        {
            windCommand.CommandText = "SELECT  marketValueInCurrency  FROM [DFZQ].[dbo].[stockDailyDerivative] where  code='" + code + "' and volume>0 and  tradingDate>='" + startDate + "' and tradingDate<='" + endDate + "' and turnOver>0 order by tradingDate asc";
            SqlDataAdapter sqlAdapter = new SqlDataAdapter(this.windCommand.CommandText, this.windConnection);
            DataTable dt = new DataTable();
            sqlAdapter.Fill(dt);

            double[] closePriceList = new double[dt.Rows.Count];
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                closePriceList[i] = Double.Parse(dt.Rows[i]["marketValueInCurrency"].ToString());
            }
            return closePriceList;
        }


        //得到单个股票某日复权收盘价
        public double[] getStockTurnOverList(string startDate, string endDate, string code)
        {
            windCommand.CommandText = "SELECT  turnOver FROM [DFZQ].[dbo].[stockDailyDerivative] where  code='" + code + "' and volume>0 and  tradingDate>='" + startDate + "' and tradingDate<='" + endDate + "' order by tradingDate asc";
            SqlDataAdapter sqlAdapter = new SqlDataAdapter(this.windCommand.CommandText, this.windConnection);
            DataTable dt = new DataTable();
            sqlAdapter.Fill(dt);

            double[] turnOverList = new double[dt.Rows.Count];
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                turnOverList[i] = Double.Parse(dt.Rows[i]["turnOver"].ToString());
            }
            return turnOverList;
        }
        //得到单个股票交易量序列
        public double[] getStockAmountList(string startDate, string endDate, string code)
        {
            windCommand.CommandText = "SELECT  amount FROM [DFZQ].[dbo].[stockDailyDerivative] where  code='" + code + "' and volume>0 and  tradingDate>='" + startDate + "' and tradingDate<='" + endDate + "' order by tradingDate asc";
            SqlDataAdapter sqlAdapter = new SqlDataAdapter(this.windCommand.CommandText, this.windConnection);
            DataTable dt = new DataTable();
            sqlAdapter.Fill(dt);

            double[] turnOverList = new double[dt.Rows.Count];
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                turnOverList[i] = Double.Parse(dt.Rows[i]["amount"].ToString());
            }
            return turnOverList;
        }


        //得到单个股票某日收盘价
        public double getStockClosePrice(string code, string tradingDate)
        {
            windCommand.CommandText = "SELECT  closePrice FROM [DFZQ].[dbo].[stockDaily] where code='" + code + "' and tradingDate<='" + tradingDate + "'";

            SqlDataReader sdr = windCommand.ExecuteReader();
            double closePrice = 0;
            while (sdr.Read())
            {
                closePrice = Double.Parse(sdr["closePrice"].ToString());
            }
            sdr.Close();
            return closePrice;
        }
        //得到单个股票某日收盘价
        public double getAdjustedStockClosePrice(string code, string tradingDate)
        {
            windCommand.CommandText = "SELECT  closePrice FROM [DFZQ].[dbo].[stockDailyDerivative] where code='" + code + "' and tradingDate<='" + tradingDate + "'";

            SqlDataReader sdr = windCommand.ExecuteReader();
            double closePrice = 0;
            while (sdr.Read())
            {
                closePrice = Double.Parse(sdr["closePrice"].ToString());
            }
            sdr.Close();
            return closePrice;
        }
        //得到某一个指数，某一日收盘价
        public double getIndexClosePrice(string indexCode, string tradingDate)
        {
            windCommand.CommandText = "SELECT  closePrice FROM [DFZQ].[dbo].[indexDaily] where indexCode='" + indexCode + "' and tradingDate='" + tradingDate + "'";

            SqlDataReader sdr = windCommand.ExecuteReader();
            double closePrice = 0;
            while (sdr.Read())
            {
                closePrice = Double.Parse(sdr["closePrice"].ToString());
            }
            sdr.Close();

            return closePrice;

        }


        //得到单个股票某日收益率
        public double getStockDailyIncrease(string code, string tradingDate)
        {
            windCommand.CommandText = "SELECT  increase FROM [DFZQ].[dbo].[stockDailyDerivative] where code='" + code + "' and tradingDate='" + tradingDate + "'";

            SqlDataReader sdr = windCommand.ExecuteReader();
            double increase = 0;
            while (sdr.Read())
            {
                increase = Double.Parse(sdr["increase"].ToString());
            }
            sdr.Close();

            return increase;
        }



        //得到某一个指数，某一日收益率
        public double getIndexDailyIncrease(string indexCode, string tradingDate)
        {
            windCommand.CommandText = "SELECT  (closePrice-lastClosePrice)/lastClosePrice as increase  FROM [DFZQ].[dbo].[indexDaily] where indexCode='" + indexCode + "' and tradingDate='" + tradingDate + "'";

            SqlDataReader sdr = windCommand.ExecuteReader();
            double increase = 0;
            while (sdr.Read())
            {
                increase = Double.Parse(sdr["increase"].ToString());
            }
            sdr.Close();

            return increase;

        }


        public List<string> getStockTradingDates(string code, string startDate = "20000101", string endDate = "20111231")
        {
            windCommand.CommandText = "SELECT tradingDate FROM [stockDaily] where tradingDate>=" + startDate + " and tradingDate<=" + endDate + " and  volume>0 and code='"+code+"' order by tradingDate asc";

            SqlDataReader sdr = windCommand.ExecuteReader();
            List<string> tradingDateList = new List<string>();
            while (sdr.Read())
            {
                tradingDateList.Add(sdr["tradingDate"].ToString());
            }
            sdr.Close();
            return tradingDateList;
        }

        public string getNearestStockTradingDate(string code, string startDate)
        {
            windCommand.CommandText = "SELECT top 1 tradingDate FROM [stockDaily] where tradingDate>=" + startDate + " and volume>0 and code='" + code + "' order by tradingDate asc";

            SqlDataReader sdr = windCommand.ExecuteReader();
            string tradingDate=null;
            while (sdr.Read())
            {
                tradingDate=sdr["tradingDate"].ToString();
            }
            sdr.Close();
            return tradingDate;
        }
        //插入资金强度值
        public void insertMoneyStrength(string code,double [] msValues,List<string> tradingDateList)
        {
            for (int i = 0; i < tradingDateList.Count; i++)
            {
                windCommand.CommandText = "insert into moneyStrength values('"+code+"','"+tradingDateList[i]+"',"+msValues[i]+")";
                windCommand.ExecuteNonQuery();
            }
        }


        //得到资金强度最新数据最大日期
        public string getMoneyStrengthMaxDate(string code)
        {
            windCommand.CommandText = "SELECT max(tradingDate) as tradingDate FROM moneyStrength where code='" + code + "'";

            SqlDataReader sdr = windCommand.ExecuteReader();
            string tradingDate = null;
            while (sdr.Read())
            {
               tradingDate=sdr["tradingDate"].ToString();
            }
            sdr.Close();
           return tradingDate;
        }




        //得到资金强度最新数据列表
        public double[] getMoneyStrengthList(string startDate, string endDate, string code)
        {
            windCommand.CommandText = "SELECT msValue  FROM moneyStrength where code='" + code + "' and tradingDate>='" + startDate + "' and tradingDate<='" + endDate + "' order by tradingDate asc ";
            SqlDataAdapter sqlAdapter = new SqlDataAdapter(this.windCommand.CommandText, this.windConnection);
            DataTable dt = new DataTable();
            sqlAdapter.Fill(dt);

            double[] msList = new double[dt.Rows.Count];
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                msList[i] = Double.Parse(dt.Rows[i]["msValue"].ToString());
            }
            return msList;

        }

    }
}

